package com.server.db;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import com.alibaba.fastjson.JSON;
import com.alibaba.fastjson.JSONArray;
import com.server.background.data.Account;
import com.server.background.data.App;
import com.server.background.data.AppRanklist;
import com.server.background.data.AppSwitch;
import com.server.background.data.RankData;
import com.server.background.data.RankTableData;
import com.server.tools.AppData;
import com.server.tools.EmojiUtils;

public class QueryData {
	public static QueryData instance;
	public static QueryData GetInstance(){
		if(instance == null){
			instance = new QueryData();
		}
		return instance;
	}

	//查询测试表数据
    public boolean QueryTestTableData() throws SQLException{ 
    	int status = 0;
    	
    	Connection conn = DBPool.getConnection();
        Statement stmt = conn.createStatement();
        
        ResultSet rs = stmt.executeQuery("SELECT data FROM test where id = 1");
        
        while(rs.next()){
        	status = rs.getInt("data");
        }
        rs.close();
        conn.commit();
        conn.close();
        
        return status==2;
    }
    
    /**
	 * 获取管理员数据
	 * @param uname
	 * @param password
	 * @return
	 * @throws SQLException
	 */
	public Account GetAccountData(String uname,String password) throws SQLException {
		Account account = new Account();
		String sql = "SELECT account_id,power,app_power FROM account where uname = ? and password = ?";
    	
    	Connection conn = DBPool.getConnection();
    	PreparedStatement ptmt = conn.prepareStatement(sql);
    	try {
    		ptmt.setString(1, uname);
    		ptmt.setString(2, password);
    		ResultSet rs = ptmt.executeQuery();
    		while (rs.next()) {
    			account.accountId = rs.getInt("account_id");
    			account.power = rs.getInt("power");
    			account.app_power = rs.getString("app_power");
    		}
    		rs.close();
    	}catch (SQLException e) {
    		e.printStackTrace();
		}
    	
    	ptmt.close();
    	conn.commit();
    	conn.close();
		return account;
	}
	
	/**
	 * 检测管理员是否存在
	 * @param uname 用户名
	 * @return
	 * @throws SQLException
	 */
	public boolean JudgeAccountExisted(String uname) throws SQLException {
		boolean isSuccess = false;
		
		String sql = "SELECT uname FROM account where uname = ?";
    	
    	Connection conn = DBPool.getConnection();
    	PreparedStatement ptmt = conn.prepareStatement(sql);
    	try {
    		ptmt.setString(1, uname);
    		ResultSet rs = ptmt.executeQuery();
    		isSuccess = rs.next();
    		rs.close();
    	}catch (SQLException e) {
    		e.printStackTrace();
		}
    	ptmt.close();
    	conn.commit();
    	conn.close();
		
		return isSuccess;
	}
	
	/**
	 * 查询管理员列表
	 * @return
	 * @throws SQLException
	 */
	public List<Account> GetAccountList() throws SQLException{
		List<Account> accountList = new ArrayList<Account>();
		
    	String sql = "SELECT * FROM account";
    	
    	Connection conn = DBPool.getConnection();
    	PreparedStatement ptmt = conn.prepareStatement(sql);
    	try {
    		ResultSet rs = ptmt.executeQuery();
    		while(rs.next()){
    			Account account = new Account();
    			account.accountId = rs.getInt("account_id");
    			account.uname = rs.getString("uname");
    			account.power = rs.getInt("power");
    			account.time = rs.getLong("time");
    			accountList.add(account);
    		}
    		rs.close();
    	}catch (SQLException e) {
    		e.printStackTrace();
		}
        
    	ptmt.close();
    	conn.commit();
    	conn.close();
    	return accountList;
    }
	
	/**
	 * 获取管理员密码
	 * @param uname
	 * @return
	 * @throws SQLException 
	 */
	public String GetAccountPassword(String uname) throws SQLException {
		String	pas = null;
		String sql = "SELECT password FROM account where uname = ?";
    	
    	Connection conn = DBPool.getConnection();
    	PreparedStatement ptmt = conn.prepareStatement(sql);
    	try {
    		ptmt.setString(1, uname);
    		ResultSet rs = ptmt.executeQuery();
    		while (rs.next()) {
    			pas = rs.getString("password");
    		}
    		rs.close();
    	}catch (SQLException e) {
    		e.printStackTrace();
		}
    	
    	ptmt.close();
    	conn.commit();
    	conn.close();
		return pas;
	}
	
	public App GetAppData(int appid) throws SQLException{
		App app = new App();
		
    	String sql = "SELECT * FROM app where id = " + appid;
    	
    	Connection conn = DBPool.getConnection();
    	PreparedStatement ptmt = conn.prepareStatement(sql);
    	try {
    		ResultSet rs = ptmt.executeQuery();
    		while(rs.next()){
    			app.appid = rs.getLong("id");
    			app.appname = rs.getString("appname");
    			app.appkey = rs.getString("appkey");
    			app.createtime = rs.getString("createtime");
    			app.createtime = app.createtime.substring(0,app.createtime.length() - 2);
    			app.wx_appid = rs.getString("wx_appid");
    			app.wx_secret = rs.getString("wx_secret");
    		}
    		rs.close();
    	}catch (SQLException e) {
    		e.printStackTrace();
		}
        
    	ptmt.close();
    	conn.commit();
    	conn.close();
    	return app;
	}
	
	/**
	 * 查询管理员列表
	 * @return
	 * @throws SQLException
	 */
	public List<App> GetAppList() throws SQLException{
		List<App> appList = new ArrayList<App>();
		
    	String sql = "SELECT * FROM app";
    	
    	Connection conn = DBPool.getConnection();
    	PreparedStatement ptmt = conn.prepareStatement(sql);
    	try {
    		ResultSet rs = ptmt.executeQuery();
    		while(rs.next()){
    			App app = new App();
    			app.appid = rs.getLong("id");
    			app.appname = rs.getString("appname");
    			app.appkey = rs.getString("appkey");
    			app.createtime = rs.getString("createtime");
    			app.createtime = app.createtime.substring(0,app.createtime.length() - 2);
    			app.wx_appid = rs.getString("wx_appid");
    			app.wx_secret = rs.getString("wx_secret");
    			appList.add(app);
    		}
    		rs.close();
    	}catch (SQLException e) {
    		e.printStackTrace();
		}
        
    	ptmt.close();
    	conn.commit();
    	conn.close();
    	return appList;
    }
	
	/**
	 * 查询管理员App权限列表
	 * @return
	 * @throws SQLException
	 */
	public List<Long> GetPowerList(int uid) throws SQLException{
		List<Long> appList = new ArrayList<Long>();
		String data = "";
    	String sql = "SELECT app_power FROM account where account_id = ?";
    	
    	Connection conn = DBPool.getConnection();
    	PreparedStatement ptmt = conn.prepareStatement(sql);
    	ptmt.setInt(1, uid);
    	try {
    		ResultSet rs = ptmt.executeQuery();
    		while(rs.next()){
    			data = rs.getString("app_power");
    		}
    		rs.close();
    	}catch (SQLException e) {
    		e.printStackTrace();
		}
        
    	ptmt.close();
    	conn.commit();
    	conn.close();
    	if(data != null && data != "") {
    		appList = JSON.parseArray(data,long.class);
    	}
    	return appList;
    }
	
	/**
	 * 查询管理员App权限列表
	 * @return
	 * @throws SQLException
	 */
	public List<Long> GetPowerList(String uname) throws SQLException{
		List<Long> appList = new ArrayList<Long>();
		String data = "";
    	String sql = "SELECT app_power FROM account where uname = ?";
    	
    	Connection conn = DBPool.getConnection();
    	PreparedStatement ptmt = conn.prepareStatement(sql);
    	ptmt.setString(1, uname);
    	try {
    		ResultSet rs = ptmt.executeQuery();
    		while(rs.next()){
    			data = rs.getString("app_power");
    		}
    		rs.close();
    	}catch (SQLException e) {
    		e.printStackTrace();
		}
        
    	ptmt.close();
    	conn.commit();
    	conn.close();
    	if(data != null && data != "") {
    		appList = JSON.parseArray(data,long.class);
    	}
    	return appList;
    }
	
	/**
	 * 获取应用开关列表
	 * @param uname 用户名
	 * @return
	 * @throws SQLException
	 */
	public boolean GetAppSwitch() throws SQLException {
		boolean isSuccess = false;
		
		String sql = "SELECT * FROM app_switch";
    	
    	Connection conn = DBPool.getConnection();
    	PreparedStatement ptmt = conn.prepareStatement(sql);
    	try {
    		ResultSet rs = ptmt.executeQuery();
    		while(rs.next()) {
    			AppSwitch as = new AppSwitch();
				as.appid = rs.getLong("id");
				as.appname = rs.getString("appname");
				as.switch_1 = rs.getByte("switch_1");
				as.switch_2 = rs.getByte("switch_2");
				as.switch_3 = rs.getByte("switch_3");
				as.switch_4 = rs.getByte("switch_4");
				as.switch_5 = rs.getByte("switch_5");
				AppData.appSwitchMap.put(as.appid, as);
    		}
    		rs.close();
    	}catch (SQLException e) {
    		e.printStackTrace();
		}
    	ptmt.close();
    	conn.commit();
    	conn.close();
		
		return isSuccess;
	}
	
	/**
	 * 获取排行榜表单
	 * @param uname 用户名
	 * @return
	 * @throws SQLException
	 */
	public boolean GetRanklist() throws SQLException {
		boolean isSuccess = false;
		
		String sql = "SELECT * FROM rklist";
    	
    	Connection conn = DBPool.getConnection();
    	PreparedStatement ptmt = conn.prepareStatement(sql);
    	try {
    		ResultSet rs = ptmt.executeQuery();
    		while(rs.next()) {
    			RankTableData rtd = new RankTableData();
    			rtd.appid = rs.getLong("appid");
    			rtd.appname = rs.getString("appname");
    			rtd.rkid = rs.getByte("id");
    			rtd.rkname = rs.getString("rkname");
    			rtd.reset_time = rs.getInt("reset_time");
    			rtd.create_time = rs.getString("create_time");
				AppData.rankTableMap.put(rtd.rkid, rtd);
    		}
    		rs.close();
    	}catch (SQLException e) {
    		e.printStackTrace();
		}
    	ptmt.close();
    	conn.commit();
    	conn.close();
		
		return isSuccess;
	}
	
	/**
	 * 获取应用列表
	 * @param uname 用户名
	 * @return
	 * @throws SQLException
	 */
	public boolean GetAppMap() throws SQLException {
		boolean isSuccess = false;
		
		String sql = "SELECT * FROM app";
    	
    	Connection conn = DBPool.getConnection();
    	PreparedStatement ptmt = conn.prepareStatement(sql);
    	try {
    		ResultSet rs = ptmt.executeQuery();
    		while(rs.next()) {
    			App app = new App();
    			app.appid = rs.getLong("id");
    			app.appname = rs.getString("appname");
    			app.appkey = rs.getString("appkey");
    			app.wx_appid = rs.getString("wx_appid");
    			app.wx_secret = rs.getString("wx_secret");
    			app.createtime = rs.getString("createtime");
    			app.createtime = app.createtime.substring(0,app.createtime.length() - 2);
				AppData.appMap.put(app.appid, app);
    		}
    		rs.close();
    	}catch (SQLException e) {
    		e.printStackTrace();
		}
    	ptmt.close();
    	conn.commit();
    	conn.close();
		
		return isSuccess;
	}
	
	/**
	 * 获取用户id
	 * @param uname 用户名
	 * @return
	 * @throws SQLException
	 */
	public long GetUID(long appid,String unionid) throws SQLException {
		long uid = -4;
		
		String sql = "SELECT id FROM users where unionid = ? and appid = ?";
    	
    	Connection conn = DBPool.getConnection();
    	PreparedStatement ptmt = conn.prepareStatement(sql);
    	try {
    		ptmt.setString(1, unionid);
    		ptmt.setLong(2, appid);
    		ResultSet rs = ptmt.executeQuery();
    		while(rs.next()) {
    			uid = rs.getLong("id");
    		}
    		rs.close();
    	}catch (SQLException e) {
    		e.printStackTrace();
    		uid = -3;
		}
    	ptmt.close();
    	conn.commit();
    	conn.close();
		
		return uid;
	}
	
	/**
	 * 检测数据是否存在
	 * @param uid
	 * @param appid
	 * @param key
	 * @return
	 * @throws SQLException
	 */
	public boolean JudgeDataExisted(long uid ,long appid, String key) throws SQLException {
		boolean isExisted =false;
		String sql = "SELECT id FROM data where appid = ? and datakey = ? and uid = ?";
		Connection conn = DBPool.getConnection();
    	PreparedStatement ptmt = null;
    	try {
    		ptmt = conn.prepareStatement(sql);
    		ptmt.setLong(1, appid);
    		ptmt.setString(2, key);
    		ptmt.setLong(3, uid);
    		ResultSet rs = ptmt.executeQuery();
    		isExisted = rs.next();
    		rs.close();
    	}catch (SQLException e) {
    		e.printStackTrace();
    		uid = -3;
		}
    	ptmt.close();
    	conn.commit();
    	conn.close();
    	return isExisted;
	}
	
	/**
	 * 获取数据
	 * @param uid
	 * @param appid
	 * @param key
	 * @return
	 * @throws SQLException
	 */
	public String GetData(long uid ,long appid, String key) throws SQLException {
		String value = null;
		String sql = "SELECT value FROM data where appid = ? and datakey = ? and uid = ?";
		Connection conn = DBPool.getConnection();
    	PreparedStatement ptmt = null;
    	try {
    		ptmt = conn.prepareStatement(sql);
    		ptmt.setLong(1, appid);
    		ptmt.setString(2, key);
    		ptmt.setLong(3, uid);
    		ResultSet rs = ptmt.executeQuery();
    		while(rs.next()) {
    			value = rs.getString("value");
    		}
    		rs.close();
    	}catch (SQLException e) {
    		e.printStackTrace();
		}
    	ptmt.close();
    	conn.commit();
    	conn.close();
    	return value;
	}
	
	/**
	 * 刷新排行榜
	 * @param rkid
	 * @param rkname
	 * @throws SQLException 
	 */
	public void RefreshRanklist(int rkid, String rkname) throws SQLException {
		List<RankData> list = new ArrayList<RankData>();
		String sql = "SELECT * FROM " + rkname + " order by value desc limit 50 ";
    	Connection conn = DBPool.getConnection();
    	PreparedStatement ptmt = null;
    	try {
			ptmt = conn.prepareStatement(sql);
	        ResultSet rs = ptmt.executeQuery();
	        while (rs.next()) {
	        	RankData rd = new RankData();
	        	rd.uid = rs.getLong("uid");
	        	rd.name = rs.getString("uname");
	        	rd.head = rs.getString("uhead");
	        	rd.value = rs.getLong("value");
	        	rd.info = rs.getString("info");
	        	list.add(rd);
	        }
	        rs.close();
		} catch (SQLException e) {
			e.printStackTrace();
		}
    	ptmt.close();
    	conn.commit();
    	conn.close();
    	for (int i = 0; i < list.size() ; i++) {
			list.get(i).name = EmojiUtils.unicode2Emoji(list.get(i).name);
		}
    	AppData.rankDataMap.put(rkid, list);
	}

	/**
	 * 获取应用注册榜单
	 * @throws SQLException 
	 */
	public List<AppRanklist> GetRegisterRank() throws SQLException {
		List<AppRanklist> list = new ArrayList<AppRanklist>();
		String sql = "SELECT * FROM app order by register_count desc";
    	Connection conn = DBPool.getConnection();
    	PreparedStatement ptmt = null;
    	try {
			ptmt = conn.prepareStatement(sql);
	        ResultSet rs = ptmt.executeQuery();
	        while (rs.next()) {
	        	AppRanklist rd = new AppRanklist();
	        	rd.appname = rs.getString("appname");
	        	rd.register_count = rs.getLong("register_count");
	        	rd.game_count = rs.getLong("game_count");
	        	rd.createtime = rs.getString("createtime");
    			rd.createtime = rd.createtime.substring(0,rd.createtime.length() - 2);
	        	list.add(rd);
	        }
	        rs.close();
		} catch (SQLException e) {
			e.printStackTrace();
		}
    	ptmt.close();
    	conn.commit();
    	conn.close();
    	return list;
	}
	
	/**
	 * 获取应用启动榜单
	 * @throws SQLException 
	 */
	public List<AppRanklist> GetGameRank() throws SQLException {
		List<AppRanklist> list = new ArrayList<AppRanklist>();
		String sql = "SELECT * FROM app order by game_count desc";
    	Connection conn = DBPool.getConnection();
    	PreparedStatement ptmt = null;
    	try {
			ptmt = conn.prepareStatement(sql);
	        ResultSet rs = ptmt.executeQuery();
	        while (rs.next()) {
	        	AppRanklist rd = new AppRanklist();
	        	rd.appname = rs.getString("appname");
	        	rd.register_count = rs.getLong("register_count");
	        	rd.game_count = rs.getLong("game_count");
	        	rd.createtime = rs.getString("createtime");
    			rd.createtime = rd.createtime.substring(0,rd.createtime.length() - 2);
	        	list.add(rd);
	        }
	        rs.close();
		} catch (SQLException e) {
			e.printStackTrace();
		}
    	ptmt.close();
    	conn.commit();
    	conn.close();
    	return list;
	}
	
	/**
	 * 获取应用启动榜单
	 * @throws SQLException 
	 */
	public int JudgeRedeem(String redeem) throws SQLException {
		int state = 1;
		String sql = "SELECT value FROM rcdata where redeem = ?";
    	Connection conn = DBPool.getConnection();
    	PreparedStatement ptmt = null;
    	try {
			ptmt = conn.prepareStatement(sql);
    		ptmt.setString(1, redeem);
	        ResultSet rs = ptmt.executeQuery();
	        while (rs.next()) {
	        	state = 0;
	        }
	        rs.close();
		} catch (SQLException e) {
			e.printStackTrace();
			state = 0;
		}
    	ptmt.close();
    	conn.commit();
    	conn.close();
    	return state;
	}
	
	/**
	 * 获取应用启动榜单
	 * @throws SQLException 
	 */
	public boolean JudgeName(String pname) throws SQLException {
		boolean state = true;
		String sql = "SELECT pname FROM pname where pname = ?";
    	Connection conn = DBPool.getConnection();
    	PreparedStatement ptmt = null;
    	try {
			ptmt = conn.prepareStatement(sql);
    		ptmt.setString(1, pname);
	        ResultSet rs = ptmt.executeQuery();
	        state = rs.next();
	        rs.close();
		} catch (SQLException e) {
			e.printStackTrace();
		}
    	ptmt.close();
    	conn.commit();
    	conn.close();
    	return state;
	}
	
	/**
	 *  判断uid是否绑定
	 * @throws SQLException 
	 */
	public boolean JudgeUidBind(Long uid) throws SQLException {
		boolean state = true;
		String sql = "SELECT uid FROM username where uid = ?";
    	Connection conn = DBPool.getConnection();
    	PreparedStatement ptmt = null;
    	try {
			ptmt = conn.prepareStatement(sql);
    		ptmt.setLong(1, uid);
	        ResultSet rs = ptmt.executeQuery();
	        state = rs.next();
	        rs.close();
		} catch (SQLException e) {
			e.printStackTrace();
		}
    	ptmt.close();
    	conn.commit();
    	conn.close();
    	return state;
	}
	
	/**
	 * 判断用户名是否重复
	 * @throws SQLException 
	 */
	public boolean JudgeUsername(String uname) throws SQLException {
		boolean state = true;
		String sql = "SELECT uid FROM username where username = ?";
    	Connection conn = DBPool.getConnection();
    	PreparedStatement ptmt = null;
    	try {
			ptmt = conn.prepareStatement(sql);
    		ptmt.setString(1, uname);
	        ResultSet rs = ptmt.executeQuery();
	        state = rs.next();
	        rs.close();
		} catch (SQLException e) {
			e.printStackTrace();
		}
    	ptmt.close();
    	conn.commit();
    	conn.close();
    	return state;
	}
	
	/**
	 * 通过帐号密码获取uid
	 * @throws SQLException 
	 */
	public Long LoginFromUsername(String uname, String pword) throws SQLException {
		long uid = 0;
		String sql = "SELECT uid FROM username where username = ? and password = ?";
    	Connection conn = DBPool.getConnection();
    	PreparedStatement ptmt = null;
    	try {
			ptmt = conn.prepareStatement(sql);
    		ptmt.setString(1, uname);
    		ptmt.setString(2, pword);
	        ResultSet rs = ptmt.executeQuery();
	        while(rs.next()){
	        	uid = rs.getLong("uid");
	        }
	        rs.close();
		} catch (SQLException e) {
			e.printStackTrace();
		}
    	ptmt.close();
    	conn.commit();
    	conn.close();
    	return uid;
	}
	

	
	/**
	 * 查询管理员列表
	 * @return
	 * @throws SQLException
	 */
	public Map<Integer, com.server.background.data.Store> GetStoreMap() throws SQLException{
		Map<Integer, com.server.background.data.Store> map = new HashMap<Integer, com.server.background.data.Store>();
		
    	String sql = "SELECT * FROM store";
    	
    	Connection conn = DBPool.getConnection();
    	PreparedStatement ptmt = conn.prepareStatement(sql);
    	try {
    		ResultSet rs = ptmt.executeQuery();
    		while(rs.next()){
    			com.server.background.data.Store store = new com.server.background.data.Store();
    			store.itemid = rs.getInt("itemid");
    			store.name = rs.getString("name");
    			store.desc = rs.getString("desc");
    			store.price = rs.getInt("price");
    			map.put(store.itemid, store);
    		}
    		rs.close();
    	}catch (SQLException e) {
    		e.printStackTrace();
		}
        
    	ptmt.close();
    	conn.commit();
    	conn.close();
    	return map;
    }
}
